<?php
Class ModelReportV2Buyer extends Model {

     /*
     * 获取基于商品的销售报表
     * 最近一周销售总数&总金额『 TQLW / TPLW 』
     * 最近四周销售总数&总金额『 TQLM / TPLM 』
     * 前年销售总数&总金额『 TQTY / TPTY 』
     * 毛利率（当年销售总价-总成本）/总价『 GPRTY 』
     * 当前库存总数&总成本『 SQN / SCN 』
     * 当前库存预计销售周期（当前库存/最近四周销量总数*7）【单位：天】『 SSC 』
     * 采购在途数量『 SIOW 』
     * 采购在途预计销售周期（采购在途数量/最近四周销量总数）【单位：周】『 SIOWSC 』
     * 原始表单样式由 @采销部蒋总 提供
     */
    public function getbuyer($inventqty,$avbuyer,$avbuyerp){
        //LD 昨天
            $ret = $inventqty;
            $sql = "SELECT OP.`quantity`, OP.`total` as 'pay_total', OP.product_code, o.`date_added`, o.`order_id` FROM `order_product` AS OP LEFT JOIN `order` AS o ON o.`order_id` = OP.`order_id` WHERE o.`order_status_id` <> 16 ";
            
            $query = $this->db->query($sql);
            $data['LD'] = date('Y-m-d', strtotime('-1 days'));
            $data['ND'] = date('Y-m-d');
            $data['LMN'] = date('Y-m');
            $data['SY'] = date('Y-m',strtotime('-1 month'));
            // var_dump( $data['SY']);
            $data['TY'] = date('Y').'-01-01';
            $data['LM'] = date('Y-m-d', strtotime('-4 week'));

            foreach ($query->rows as $row) {
                $date = $row['date_added'];
                $pcode = substr($row['product_code'], 0, 10);
                $pclass = $avbuyerp[$pcode]['product_class1'];
                // var_dump($avbuyerp[$pcode]['product_cost']);
                // die();
                //
                if ($avbuyerp[$pcode]['product_type']!=2) {



                    $s1 = strtotime($date);
                    // var_dump($data['TY']);
                    $s2 = strtotime($data['TY']);
                    // var_dump($s2);
                    if ($s1>=$s2) {
                        // $j++;
                

                        $ret[$pclass]['TQTY'] += $row['quantity'];
                        $ret[$pclass]['TPTY'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLTY'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCTY'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCTY'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQTY'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPTY'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLTY'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCTY'.'-n'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCTY'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                            }
                        }
     
                        
                    
                    }
                        $lmn = substr($row['date_added'],0,10);
                        // var_dump($lmn);
                        $lmn = strtotime($lmn);
                        $dlmn = strtotime( $data['LD']);
                    if ($lmn==$dlmn) {
                        $ret[$pclass]['TQLD'] += $row['quantity'];
                        $ret[$pclass]['TPLD'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLLD'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCLD'] += $row['TQLD']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCLD'] += $row['TQLD']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQLD'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPLD'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLLD'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCLD'.'-n'] += $row['TQLD']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCLD'.'-n'] += $row['TQLD']*$avbuyer[$pcode]['buyav'];
                            }
                        }

                    
                    }

                    $lmn = substr($row['date_added'],0,10);
                        // var_dump($lmn);
                        $lmn = strtotime($lmn);
                        $dlmn = strtotime( $data['ND']);
                    if ($lmn==$dlmn) {
                        $ret[$pclass]['TQND'] += $row['quantity'];
                        $ret[$pclass]['TPND'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLND'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCND'] += $row['TQND']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCND'] += $row['TQND']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQND'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPND'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLND'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCND'.'-n'] += $row['TQND']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCND'.'-n'] += $row['TQND']*$avbuyer[$pcode]['buyav'];
                            }
                        }

                    
                    }


                                       // var_dump($data['LMN']);
                    $sy = substr($row['date_added'],0,7);
                        // var_dump($lmn);
                    // $lmn = strtotime($row['date_added']);
                    $dsy=  $data['SY'];
                        // var_dump($lmn);
                        // var_dump($dlmn);
                        // die();
                    if ($sy===$dsy) {

                        $ret[$pclass]['TQSY'] += $row['quantity'];
                        $ret[$pclass]['TPSY'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLSY'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCSY'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCSY'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }
                    if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                        $ret[$pclass]['TQSY'.'-n'] += $row['quantity'];
                        $ret[$pclass]['TPSY'.'-n'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLSY'.'-n'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCSY'.'-n'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCSY'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }  
                    }
                       
                    
                    }
                        
                        // var_dump($data['LMN']);
                        $lmn = substr($row['date_added'],0,7);
                        // var_dump($lmn);
                        $lmn = strtotime($lmn);
                        $dlmn = strtotime( $data['LMN']);
                        // var_dump($lmn);
                        // var_dump($dlmn);
                        // die();
                        
                    if ($lmn==$dlmn) {

                        $ret[$pclass]['TQLMN'] += $row['quantity'];
                        $ret[$pclass]['TPLMN'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLLMN'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCLMN'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCLMN'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }
                    if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                        $ret[$pclass]['TQLMN'.'-n'] += $row['quantity'];
                        $ret[$pclass]['TPLMN'.'-n'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLLMN'.'-n'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCLMN'.'-n'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCLMN'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }  
                    }
                        
                    
                    }

                    

  


                    $lmn = substr($row['date_added'],0,7);
                        // var_dump($lmn);
                    $lmn = strtotime($lmn);
                    $dlmn = strtotime( $data['LM']);
                        // var_dump($lmn);
                        // var_dump($dlmn);
                        // die();
                    if ($lmn>=$dlmn) {

                        $ret[$pclass]['TQLM'] += $row['quantity'];
                    
                    }



                    
                   
                }
                
            }
            $sql = "SELECT og.`quantity`, og.`pay_total`, og.product_code, og.product_id, o.date_added FROM `order_product` AS OP LEFT JOIN `order` AS o ON o.`order_id` = OP.`order_id` LEFT JOIN `order_product_group` AS og ON og.`order_product_id` = OP.`order_product_id` WHERE o.`order_status_id` <> 16 AND og.product_code !=''";
            $query = $this->db->query($sql);

            foreach ($query->rows as $row) {
                $date = $row['date_added'];
                $pcode = substr($row['product_code'], 0, 10);
                $pclass = $avbuyerp[$pcode]['product_class1'];
                // var_dump($avbuyerp[$pcode]['product_cost']);
                // die();
                //
                if (1) {

                    $s1 = strtotime($date);
                    // var_dump($data['TY']);
                    $s2 = strtotime($data['TY']);
                    // var_dump($s2);
                    if ($s1>=$s2) {
                        // $j++;
                        $ret[$pclass]['TQTY'] += $row['quantity'];
                        $ret[$pclass]['TPTY'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLTY'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCTY'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCTY'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQTY'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPTY'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLTY'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCTY'.'-n'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCTY'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                            }
                      
                        }
                        
                    }

                        $lmn = substr($row['date_added'],0,10);
                        // var_dump($lmn);
                        $lmn = strtotime($lmn);
                        $dlmn = strtotime( $data['LD']);
                    if ($lmn==$dlmn) {
                        $ret[$pclass]['TQLD'] += $row['quantity'];
                        $ret[$pclass]['TPLD'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLLD'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCLD'] += $row['TQLD']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCLD'] += $row['TQLD']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQLD'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPLD'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLLD'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCLD'.'-n'] += $row['TQLD']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCLD'.'-n'] += $row['TQLD']*$avbuyer[$pcode]['buyav'];
                            } 
                        }
       
                    
                    }

                    $lmn = substr($row['date_added'],0,10);
                        // var_dump($lmn);
                        $lmn = strtotime($lmn);
                        $dlmn = strtotime( $data['ND']);
                    if ($lmn==$dlmn) {
                        $ret[$pclass]['TQND'] += $row['quantity'];
                        $ret[$pclass]['TPND'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLND'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCND'] += $row['TQND']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCND'] += $row['TQND']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQND'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPND'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLND'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCND'.'-n'] += $row['TQND']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCND'.'-n'] += $row['TQND']*$avbuyer[$pcode]['buyav'];
                            }
                        }

                    
                    }
                        
                        // var_dump($data['LMN']);
                        $lmn = substr($row['date_added'],0,7);
                        // var_dump($lmn);
                        $lmn = strtotime($lmn);
                        $dlmn = strtotime( $data['LMN']);
                        // var_dump($lmn);
                        // var_dump($dlmn);
                        // die();
                        
                    if ($lmn==$dlmn) {

                        $ret[$pclass]['TQLMN'] += $row['quantity'];
                        $ret[$pclass]['TPLMN'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLLMN'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCLMN'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCLMN'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                             $ret[$pclass]['TQLMN'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPLMN'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLLMN'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCLMN'.'-n'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCLMN'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                            }
                        }
      
                    
                    }

                                       // var_dump($data['LMN']);
                     $sy = substr($row['date_added'],0,7);
                        // var_dump($lmn);
                    // $lmn = strtotime($row['date_added']);
                    $dsy=  $data['SY'];
                        // var_dump($lmn);
                        // var_dump($dlmn);
                        // die();
                    if ($sy===$dsy) {

                        $ret[$pclass]['TQSY'] += $row['quantity'];
                        $ret[$pclass]['TPSY'] += round($row['pay_total'], 2);
                        $ret[$pclass]['TPXLSY'] += round($row['pay_total'], 2);
                        if (empty($avbuyer[$pcode]['buyav'])) {
                            $ret[$pclass]['TCSY'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                            
                        }else{
                            $ret[$pclass]['TCSY'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                        }
                        if ($pcode!='2101032234'||$pcode!='2101032233'||$pcode!='9601010001') {
                            $ret[$pclass]['TQSY'.'-n'] += $row['quantity'];
                            $ret[$pclass]['TPSY'.'-n'] += round($row['pay_total'], 2);
                            $ret[$pclass]['TPXLSY'.'-n'] += round($row['pay_total'], 2);
                            if (empty($avbuyer[$pcode]['buyav'])) {
                                $ret[$pclass]['TCSY'.'-n'] += $row['quantity']*$avbuyerp[$pcode]['product_cost'];
                                
                            }else{
                                $ret[$pclass]['TCSY'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                            }  
                        }

                    
                    }



                    $lmn = substr($row['date_added'],0,7);
                        // var_dump($lmn);
                    $lmn = strtotime($lmn);
                    $dlmn = strtotime( $data['LM']);
                        // var_dump($lmn);
                        // var_dump($dlmn);
                        // die();
                    if ($lmn>=$dlmn) {

                        $ret[$pclass]['TQLM'] += $row['quantity'];
                    
                    }



                    
                   
                }
                
            }
             // echo time(); echo '</br>';

        
        
         // echo time(); echo '</br>';
        foreach ($ret as $GPRTY=>$info) {
            // var_dump($info);

                // if ((int)$info['TQTY'] < 1) {
                //     continue;
                // }
            // var_dump($ret[$GPRTY]['TPSY-n']);
            // var_dump($ret[$GPRTY]['TCSY-n']);
                if (!empty($ret[$GPRTY]['TCTY'])) {
                    $info['GPRTY'] = (round(($ret[$GPRTY]['TPTY-n']-$ret[$GPRTY]['TCTY-n'])/$ret[$GPRTY]['TPTY-n'], 4)*100).'%';
                    $info['GPRLMN'] = (round(($ret[$GPRTY]['TPLMN-n']-$ret[$GPRTY]['TCLMN-n'])/$ret[$GPRTY]['TPLMN-n'], 4)*100).'%';
                    $info['GPRSY'] = (round(($ret[$GPRTY]['TPSY-n']-$ret[$GPRTY]['TCSY-n'])/$ret[$GPRTY]['TPSY-n'], 4)*100).'%';
                }
                
                $info['name']=M('category_description')->where(array('category_id' =>$GPRTY ))->getField('name');

                $ret[$GPRTY]['SQN'] = $info['availableQty'];
                // $info['SCN'] = $info['availableQty']*$info['cost'];

                if ($info['TQLM']) {
                    $info['SSC'] = round($ret[$GPRTY]['SQN']/$info['TQLM']*28, 2);
                }else{
                    $info['SSC'] = '--';
                }

                $info['TPXLTY'] = number_format($info['TPXLTY']); 
                $info['TPXLLD'] = number_format($info['TPXLLD']); 
                $info['TPXLSY'] = number_format($info['TPXLSY']); 
                $info['TPXLLMN'] = number_format($info['TPXLLMN']); 
                switch ($GPRTY) {
                    case '257':
                    case '258':
                    case '264':
                    case '266':

                       $arrn['YL'][] = $info;
                        break;
                    case '259':
                    case '263':
                    case '265':
                       $arrn['DM'][] = $info; 
                        break;
                    case '260':
                    case '267':
                       $arrn['ZDM'][] = $info; 
                        break;

                    
                    case '789':
                        $arrn['QT'][] = $info; 
                        break;
                     default:
                        $arrn['QT'][] = $info; 
                        break;
                }
        }
        // var_dump($arrn);die();
       return $arrn;
        
    }

    public function getInvenTotal($pobuyer){
        $ret = $pobuyer;
        $sql = "SELECT SUM(I.`available_quantity`) as available_quantity ,SUM(I.`available_quantity`*VP.`product_cost`) as available_price , P.product_class1 FROM `inventory` AS I LEFT JOIN `product_option_value` AS O ON O.`product_code` = I.`product_code` LEFT JOIN `product` AS P ON P.`product_id` = O.`product_id` LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = P.`product_id` GROUP BY    P.product_class1"; 
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['product_class1'];
            if (!empty($pclass)) {
                $ret[$pclass]['availableQty'] = max(0, $row['available_quantity']);
                $ret[$pclass]['totalprice'] = max(0, $row['available_price']);
                $ret[$pclass]['totalpricexs'] = number_format(max(0, $row['available_price']));
            }
        }
        return $ret;
    }

    public function getstockTotal($pobuyer){
        $ret = $pobuyer;
        $sql = "SELECT P.product_class1, SUM(sd.product_quantity*sd.product_price) as 'stockprice' FROM stock_out_detail AS sd LEFT JOIN stock_out AS so ON so.out_id = sd.out_id LEFT JOIN product_option_value AS O ON sd.product_code = O.product_code LEFT JOIN product AS P ON P.product_id = O.product_id LEFT JOIN vendor AS VP ON VP.vproduct_id = P.product_id WHERE so.`status`=1 GROUP BY P.product_class1 ";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['product_class1'];
            if (!empty($pclass)) {
                $ret[$pclass]['stockpricexs'] = number_format(max(0, $row['stockprice']));
                $ret[$pclass]['stockprice'] =max(0, $row['stockprice']);
            }
        }
        return $ret;
    }

    public  function getPobuyer(){
        $sql =  "SELECT po_product.product_code, SUM(po_product.price) AS price, p.product_class1, SUM(po_product.qty*po_product.unit_price - po_product.delivered_qty*po_product.unit_price) AS buyqty FROM po_product LEFT JOIN po ON po_product.po_id = po.id LEFT JOIN product_option_value AS pv on pv.product_code = po_product.product_code LEFT JOIN product AS p  ON p.product_id = pv.product_id WHERE (po.`status` != 6 AND po.`status` != 9 AND po.`status` != 4 ) GROUP BY p.product_class1";
            // echo $sql;die();
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['product_class1'];
            if (!empty($pclass)) {
                $ret[$pclass]['pobuyprice'] = max(0, $row['buyqty']);
                $ret[$pclass]['pobuypricexs'] = number_format(max(0, $row['buyqty']));
            }
        }
        return $ret;
    }


     /*
     * 根据商品编码，获取平均进货价getsaleqty($result['product_id'])
     *
     */
    public function getAgvBuy()
    {

        $sql = "SELECT po_product.product_code, SUM(po_product.unit_price* po_product.delivered_qty)/SUM(po_product.delivered_qty) as 'buyav' FROM po_product GROUP BY po_product.product_code";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
                $ret[$pCode]['buyav']= max(0, $row['buyav']);
                
            }
    
        }
        return $ret;
    } 


         /*
     * 根据商品编码，获取成本价
     *
     */
    public function getAgvBuyp()
    {

        $sql = "SELECT product.product_code,product.product_class1, vendor.product_cost,product.product_type FROM product LEFT JOIN vendor ON product.product_id = vendor.vproduct_id ";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
                $ret[$pCode]['product_cost']= max(0, $row['product_cost']);
                $ret[$pCode]['product_class1']=  $row['product_class1'];
                $ret[$pCode]['product_type']=  $row['product_type'];
                
            }
    
        }
        return $ret;
    } 

         /*
     *
     *
     */
    public function getbuyeReport($avbuyer,$d)
    {
            
        $sql = "SELECT SUM(OP.`quantity`) AS 'quantity', SUM(OP.`pay_total`) AS 'pay_total', SUM(VP.`product_cost` * OP.`quantity` ) AS 'product_cost', P.product_class1,  P.product_code, VP.`product_cost` FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `product_option_value` AS PV ON P.`product_id` = PV.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE P.product_type =1 AND   OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND DATE_FORMAT(`date_added`, '%Y-%m')  ='".$d."' ) GROUP BY  P.product_code ";
        // echo $sql;die();
            
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            // var_dump($row);
            $pclass = $row['product_class1'];
            $pcode = $row['product_code'];
            // var_dump($pclass);die();
            if ((!empty($pclass))&&!(empty($pcode))) {
                // var_dump($avbuyer);die();
                $ret[$pclass]['quantity'] += $row['quantity'];
                $ret[$pclass]['pay_total'] += round($row['pay_total'], 2);
                $ret[$pclass]['product_cost'] += round($row['product_cost'], 2);
                if (empty($avbuyer[$pcode]['buyav'])) {
                    $ret[$pclass]['TC'] += $row['quantity']*$row['product_cost'];
                    
                }else{
                    $ret[$pclass]['TC'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                }
                    // $ret[$pclass]['TC'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                
                $ret[$pclass]['name'] = $row['name'];
                if ($pcode=='2101032234'||$pcode=='2101032233'||$pcode=='9601010001') {
                    continue;
                }
                    $ret[$pclass]['quantity'.'-n'] += $row['quantity'];
                    $ret[$pclass]['TP'.'-n'] += round($row['TP'], 2);
                    $ret[$pclass]['TPXL'.'-n'] += round($row['TP'], 2);
                    if (empty($avbuyer[$pcode]['buyav'])) {
                        $ret[$pclass]['TC'.'-n'] += $row['quantity']*$row['product_cost'];
                        
                    }else{
                        $ret[$pclass]['TC'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                    }
                        // $ret[$pclass]['TC'.$k] += $row['quantity'.$k]*$avbuyer[$pcode]['buyav'];
                    
                    $ret[$pclass]['name'] = $row['name'];
               
            }
            
        }


        $sql = "SELECT SUM(og.`quantity`) AS 'quantity', SUM(og.`pay_total`) AS 'pay_total', SUM(VP.`product_cost` * og.`quantity` ) AS 'product_cost', og.product_code, og.product_id FROM `order_product` AS OP LEFT JOIN `order_product_group` AS og ON og.`order_product_id` = OP.`order_product_id` LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = og.`product_id` LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` WHERE P.product_type = 2 AND OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND DATE_FORMAT(`date_added`, '%Y-%m') = '".$d."') GROUP BY og.product_code";
        // echo $sql;die();
            
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            // var_dump($row);

            $sql1 = "SELECT product_class1 FROM product WHERE product_id =".$row['product_id'];
            // echo $sql1;
            $query1 = $this->db->query($sql1);
            // var_dump($query1->row['product_class1']);
            $pclass = $query1->row['product_class1'];
            // var_dump( $pclass);die();
            $pcode = $row['product_code'];
            // var_dump($pcode);die();
            if ((!empty($pclass))&&!(empty($pcode))) {
                // var_dump($avbuyer);die();
                $ret[$pclass]['quantity'] += $row['quantity'];
                $ret[$pclass]['pay_total'] += round($row['pay_total'], 2);
                $ret[$pclass]['pay_totalXL'] += round($row['pay_total'], 2);
                if (empty($avbuyer[$pcode]['buyav'])) {
                    $ret[$pclass]['TC'] += $row['quantity']*$row['product_cost'];
                    
                }else{
                    $ret[$pclass]['TC'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                }
                    // $ret[$pclass]['TC'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                
                // $ret[$pclass]['name'] = $row['name'];
                if ($pcode=='2101032234'||$pcode=='2101032233'||$pcode=='9601010001') {
                    continue;
                }
                    $ret[$pclass]['quantity'.'-n'] += $row['quantity'];
                    $ret[$pclass]['pay_total'.'-n'] += round($row['pay_total'], 2);
                    $ret[$pclass]['pay_totalXL'.'-n'] += round($row['pay_total'], 2);
                    if (empty($avbuyer[$pcode]['buyav'])) {
                        $ret[$pclass]['TC'.'-n'] += $row['quantity']*$row['product_cost'];
                        
                    }else{
                        $ret[$pclass]['TC'.'-n'] += $row['quantity']*$avbuyer[$pcode]['buyav'];
                    }
                        // $ret[$pclass]['TC'.$k] += $row['quantity'.$k]*$avbuyer[$pcode]['buyav'];
                    
                    // $ret[$pclass]['name'] = $row['name'];
               
            }
            
        }
        foreach ($ret as $key => $value) {
            // var_dump($value);
            $data = array(
                'product_class1' => $key, 
                'year_month' =>date('Y-m-d H:i:s',strtotime($d)) , 
                'sale_total' =>$value['pay_total'] , 
                'name' =>$value['name'] , 
                'sale_lack_total' => $value['pay_total-n'], 
                'in_total' => $value['TC-n'], 
                'gross_profit' => (round(($value['pay_total-n']-$value['TC-n'])/$value['pay_total-n'], 4)*100).'%', 
            );
            // var_dump($data);die();
            M('buyer_report')->data($data)->add();
            
        }
    } 





    public function getbuyerReport($pclass)
    {
        $sql = "SELECT*FROM buyer_report";
            $ret =[];
            
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            // echo 1;
            // var_dump($row);
                        // var_dump($query->rows);
            $pclass = $row['product_class1'];
            // var_dump($pclass);
            $year_month = substr($row['year_month'],0,7);
            // var_dump($year_month);die();
            // $pcode = $row['product_code'];
            // var_dump($pcode);die();
            if ((!empty($pclass))&&!(empty($year_month))) {
                // var_dump($avbuyer);die();
                $ret[$pclass][$year_month] = $row['sale_total'];
                $ret[$pclass][$year_month.'ml'] = $row['gross_profit'];
               
            }
            // var_dump()
            
        }
        // var_dump($ret);die();
        return $ret;
    } 

    
}
